# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/7/28 23:15'

'''
客户经营情况报表
'''

import datetime
from common.excel_helper import ExcelReader
#from config import *
from common.mssql_helper import g_msql_inst
from common.utils import format_float, create_uuid
import re
from k3_data_report.finance.utils import getLastMonth
from k3_data_report.finance.utils import store_finance_tb_db, check_month_data_exist
from k3_data_report.finance.utils import cal_finance_jine_select
from k3_data_report.finance.config import finace_cal_fields
from k3_data_report.finance.utils import get_exvalue,ysk_maoli_by_ydtype
from collections import defaultdict
from k3_data_report.finance.utils import charge_xs_ysk_by_jiesuan_type
import math
from common.logger_helper import g_wlogger

def  cal_month_ws_ml(xinhao_dis_map, maoli_select_list):
    kehu_weishui_res_dict = defaultdict(list)
    for (ghdw, ggxh, sl, jshj) in maoli_select_list:
        weishui_price = xinhao_dis_map.get(ggxh, 0)
        weishui_jine = float(jshj) - float(weishui_price) * float(sl)
        kehu_weishui_res_dict[ghdw].append(weishui_jine)
    return kehu_weishui_res_dict

def cal_kh_business_process(last_month):
        '''
        :param last_month: 上一个月
        :return:
        '''
        year = last_month.split("-")[0]
        month = last_month.split("-")[1]
        kh_business_select_sql = finace_cal_fields['kh_business']['business_skd_select'] % last_month
        business_skd_select_list = g_msql_inst.fetch_all(kh_business_select_sql)
        if business_skd_select_list == None:
            print("获取收款单为空！")
            return

        #获取采购订单的产品型号和未税单价关系
        cgdd_xh_ws_select_sql = finace_cal_fields['kh_business']['weishui_price_select']
        weishui_price_select_list = g_msql_inst.fetch_all(cgdd_xh_ws_select_sql)
        xinhao_dis_map = {}
        for (xinhao, wprice) in weishui_price_select_list:
            xinhao_dis_map[xinhao] = wprice


        #获取合同额度
        business_k3_xsdd_select_sql = finace_cal_fields['kh_business']['business_k3_xsdd_select'] % last_month
        business_k3_xsdd_dict = cal_finance_jine_select(business_k3_xsdd_select_sql)

        #获取毛利
        hetong_maoli_select_sql = finace_cal_fields['kh_business']['hetong_maoli_select'] % last_month
        hetong_maoli_select_list = g_msql_inst.fetch_all(hetong_maoli_select_sql)
        kehu_weishui_res_dict = cal_month_ws_ml(xinhao_dis_map, hetong_maoli_select_list)

        #获取出库额
        business_k3_cke_select_sql = finace_cal_fields['kh_business']['business_k3_chukue_select'] % last_month
        business_k3_cke_dict = cal_finance_jine_select(business_k3_cke_select_sql)

        # 获取出库毛利
        business_k3_ckml_select_sql = finace_cal_fields['kh_business']['busines_k3_ck_wsje_select'] % last_month
        ck_weishui_ml_dict = cal_finance_jine_select(business_k3_ckml_select_sql)

        #获取收款额
        business_k3_shoukd_select_sql = finace_cal_fields['kh_business']['business_k3_shoukuandan'] % last_month
        business_k3_shoukd_dict = cal_finance_jine_select(business_k3_shoukd_select_sql)

        #TODO 收款毛利
        ysk_kehu_info_dict = ysk_maoli_by_ydtype(last_month)

        #本月应收款
        ysk_result_dict = charge_xs_ysk_by_jiesuan_type(last_month)

        #下月应开票
        skd_next_month_ykp_sql = finace_cal_fields['kh_business']['ykfp_next_month_select'] % last_month
        skd_next_month_ykp_dict = cal_finance_jine_select(skd_next_month_ykp_sql)

        #kh_code, kh_name, yewu_name, jiesuan_type, bm_name, bibie
        for (scode, kehuname, khname, quanqi, depart, paytype) in business_skd_select_list:
            #print(f"kehuname:{kehuname}")
            #本月合同额
            month_hetonge = business_k3_xsdd_dict.get(kehuname, 0)

            #本月合同毛利
            ht_maoli_jine = kehu_weishui_res_dict.get(kehuname, 0)

            ht_maoli_jine = sum(ht_maoli_jine) if ht_maoli_jine !=  0 else ht_maoli_jine

            #本月出库额
            month_cke_jine = business_k3_cke_dict.get(kehuname,0)

            #本月出库毛利
            ck_maoli_jine =  ck_weishui_ml_dict.get(kehuname, 0)

            #本月收款
            month_shoukd_jine = business_k3_shoukd_dict.get(scode, 0)

            #本月收款毛利
            sk_ml_month_jine_list = ysk_kehu_info_dict.get(kehuname, 0)
            sk_ml_month_jine = float("%.2f" % math.fabs(sum(sk_ml_month_jine_list) if sk_ml_month_jine_list != 0 else 0))

            #TODO 本月应收款，需要从应收款明细表中获取到
            ysk_month_jine = 0
            if quanqi == "款到发货" or quanqi == "款到订货":
                ysk_bymonth_dict = ysk_result_dict[1]
                ysk_month_jine = ysk_bymonth_dict.get(kehuname, 0)
            elif quanqi == "货到付款":
                ysk_bymonth_dict = ysk_result_dict[3]
                ysk_month_jine = ysk_bymonth_dict.get(kehuname, 0)
            else:
                ysk_bymonth_dict = ysk_result_dict[2]
                ysk_month_jine = ysk_bymonth_dict.get(kehuname, 0)

            #TODO  月份到期应收款，月份超期应收
            yfdqyinsk = 0
            yfcqyinsk = 0

            # 下月应开票
            next_month_ykp_jine = skd_next_month_ykp_dict.get(kehuname, 0)

            id = create_uuid()
            cur_time = datetime.datetime.now().strftime('%Y-%m-%d')
            kh_business_select_sql =  finace_cal_fields['kh_business']['business_jyqk_insert']
            if paytype == "人民币":
                #id, djrq, crttime, scode, kehuname, depart, khname, kuanqi,  mhetonge, monthml,  ycke, ymaoli,"
                #               " yske, skyml, myysk, yfdqysk,yfcqys,yfykfp,paytype,ymonth

                value_tuple = (id, cur_time, scode, kehuname, depart, khname,
                               quanqi, month_hetonge, ht_maoli_jine, month_cke_jine,
                               ck_maoli_jine, month_shoukd_jine,sk_ml_month_jine,ysk_month_jine,
                               yfdqyinsk, yfcqyinsk,  next_month_ykp_jine, paytype, last_month)
            else:
                value_tuple = (id, cur_time, scode, kehuname, depart, khname,
                               quanqi, month_hetonge, ht_maoli_jine, month_cke_jine,
                               ck_maoli_jine, month_shoukd_jine, sk_ml_month_jine, ysk_month_jine,
                               yfdqyinsk, yfcqyinsk, next_month_ykp_jine, paytype, last_month)
            # mstr = f"客户编码：{scode}, paytype:{paytype},quanqi:{quanqi}, 客户名称：{kehuname}, 客户经理:{khname}, 款期:{quanqi}, " \
            #     f"部门:{depart}, 支付币别:{paytype}，本月合同额:{month_hetonge}, " \
            #     f"本月合同毛利:{ht_maoli_jine}, 本月出库额:{month_cke_jine}, " \
            #     f"本月出库毛利:{ck_maoli_jine}, 本月收款额:{month_shoukd_jine}, " \
            #     f"本月应收款{ysk_month_jine}, 本月收款毛利:{sk_ml_month_jine}, " \
            #     f"下月应开票：{next_month_ykp_jine}"
            print(kh_business_select_sql)
            print(value_tuple)
            store_finance_tb_db(kh_business_select_sql, value_tuple)

        g_wlogger.winfo(f"cal_kh_business_process process success with month:{last_month}")


def cal_kh_process_main(the_month):
    try:
        from k3_data_report.business.cgtj_db import Cgtj
        check_sql = "select distinct ymonth from k3jyqks where ymonth='%s'" % the_month
        check_dic = Cgtj.check_cf(check_sql)
        if len(check_dic) > 0:  # 有数据，不需要插入
            g_wlogger.winfo(f"cal_caigou_fkd_process_main already existed with month:{the_month}.")
            return
        cal_kh_business_process(the_month)
        return True
    except Exception as e:
        g_wlogger.werror(f"cal_kh_process_main process error:{e} with:{the_month}")
        return False
